Acala Income

Row

7D Fee Income

7D Sum FeeIncome_ACA 7D Sum Stability_Fee_USD 7D Sum CDP_Penalty_Fee_USD
4,823.648 368.3302 27,690.9

Row

30D Fee Income

30D Sum FeeIncome_ACA 30D Sum Stability_Fee_USD 30D Sum CDP_Penalty_Fee_USD
12,524.74 1,001.717 52,241.4

Row

Daily Fee Data

date Block Time Balance FeeIncome Stability_Fee_USD CDP_Penalty_Fee_USD
2022-05-21 1,065,695 2022-05-21 19:48:42 34,828.90 187.64004 15.78290 0.00
2022-05-22 1,072,673 2022-05-22 19:50:18 35,002.04 173.13984 14.78307 0.00
2022-05-23 1,079,643 2022-05-23 19:55:54 35,062.67 60.63218 43.22957 0.00
2022-05-24 1,086,604 2022-05-24 19:59:00 35,747.68 685.01608 18.64677 0.00
2022-05-25 1,093,547 2022-05-25 19:59:18 36,058.39 310.70725 16.85503 0.00
2022-05-26 1,100,466 2022-05-26 19:57:48 36,167.30 108.90451 25.70315 0.00
2022-05-27 1,107,370 2022-05-27 19:57:54 37,118.37 951.07669 24.88222 90.15
2022-05-28 1,114,252 2022-05-28 19:54:30 37,213.13 94.76180 13.24859 0.00
2022-05-29 1,121,138 2022-05-29 19:54:48 37,255.86 42.72129 15.33530 0.00
2022-05-30 1,128,032 2022-05-30 19:57:48 37,633.90 378.04605 33.64274 0.00
2022-05-31 1,134,938 2022-05-31 19:57:24 37,955.70 321.79441 28.68327 0.00
2022-06-01 1,141,676 2022-06-01 19:51:48 37,998.69 42.99445 25.26076 0.00
2022-06-02 1,148,530 2022-06-02 19:58:48 38,067.28 68.58652 22.91681 0.00
2022-06-03 1,155,360 2022-06-03 19:58:48 40,475.48 2,408.20375 16.57915 21.15
2022-06-04 1,162,190 2022-06-04 19:56:12 40,837.04 361.55892 56.39135 0.00
2022-06-05 1,168,944 2022-06-05 19:50:18 40,884.71 47.67321 8.66235 0.00
2022-06-06 1,175,759 2022-06-06 19:59:06 41,051.20 166.48402 13.93945 0.00
2022-06-07 1,182,502 2022-06-07 19:58:00 41,353.38 302.18157 20.64769 0.00
2022-06-08 1,189,272 2022-06-08 19:57:18 41,454.05 100.66934 16.56430 0.00
2022-06-09 1,196,021 2022-06-09 19:59:36 41,860.35 406.30346 18.42858 0.00
2022-06-10 1,202,807 2022-06-10 19:58:18 41,999.31 138.95604 47.37496 34.35
2022-06-11 1,209,559 2022-06-11 19:55:30 42,085.09 85.78525 52.85463 1,860.75
2022-06-12 1,216,308 2022-06-12 19:59:42 42,342.35 257.25735 82.97400 22,544.10
2022-06-13 1,222,966 2022-06-13 19:57:12 43,515.05 1,172.70449 101.34596 27,219.60
2022-06-14 1,229,620 2022-06-14 19:57:12 43,990.13 475.07833 25.87389 0.00
2022-06-15 1,236,319 2022-06-15 19:59:42 44,374.38 384.24243 152.87862 0.00
2022-06-16 1,242,988 2022-06-16 19:56:00 45,139.68 765.30047 13.45169 9.30
2022-06-17 1,249,646 2022-06-17 19:59:24 45,715.47 575.79029 13.23458 0.00
2022-06-18 1,256,309 2022-06-18 19:55:00 46,431.70 716.23079 46.33412 462.00
2022-06-19 1,263,002 2022-06-19 19:55:36 47,166.00 734.30143 15.21134 0.00

Acala Balances

Row

Balance for account 23M5ttkmR6Kco5p3LFGKMpMv4zvLkKdUQWW1wGGoV8zDX3am

tokenId timestamp free price balanceUSD
LDOT 2022-06-19T00:00:00 67.16689 0.7801737 52.40184

Row

Balance for account 23M5ttkmR6KcnvsNJdmYTpLo9xfc54g8uCk55buDfiJPon69

tokenId timestamp free price balanceUSD
LDOT 2022-06-19T00:00:00 67.16689 0.7801737 52.40184
---
title: "Acala / Karura Income Statement"
output:
  flexdashboard::flex_dashboard:
    orientation: rows
    vertical_layout: scroll
    social: menu
    source_code: embed
params:
  network: Acala
---

```{css custom1, echo=FALSE}
.dataTables_scrollBody {
    max-height: 100% !important;
}
```

```{r global, include=FALSE}
library(knitr)
knitr::opts_chunk$set(
  message = FALSE,
  warning = FALSE,
  comment = "#>"
)

library(dygraphs)
library(kableExtra)
library(formattable)
library(lubridate)
library(flexdashboard)
library(DT)
library(subscanr)
library(formattable)
library(ghql)
x <- GraphqlClient$new()

# Helper function to concat
`%+%` <- function(a, b) paste0(a, b)

library(reticulate)
# use_python("/opt/homebrew/bin/python3.9")

network <- params$network

# define constants
if (tolower(network) == 'acala') {
  dex_endpoint <- "https://api.subquery.network/sq/AcalaNetwork/acala-dex-subql"
  nativeToken <- "ACA"
  api_url = 'wss://acala-rpc-0.aca-api.network'
  addr1 <- "23M5ttkmR6Kco5p3LFGKMpMv4zvLkKdUQWW1wGGoV8zDX3am"
  addr2 <- "23M5ttkmR6KcnvsNJdmYTpLo9xfc54g8uCk55buDfiJPon69"

} else if (tolower(network) == 'karura') {
  dex_endpoint <- "https://api.subquery.network/sq/AcalaNetwork/karura-dex-subql"
  nativeToken <- "KAR"
  api_url = 'wss://karura.polkawallet.io'
  addr1 <- "qmmNufxeWaAUy17XBWGc1q4n2dkdxNS2dPkhtzCZRoExdAs"
  addr2 <- "qmmNufxeWaAUp4SVa1Vi1owrzP1xhR6XKdorEcck17RF498"
  
}

# Get prices & blocks
method <- "tokenDailyData"
edges <- "tokenId price timestamp updateAtBlockId"
prices <- get_graph(dex_endpoint, method, edges, window = 31, filter = '')
prices[, block := as.numeric(updateAtBlockId) - 1]
prices[, date := as.Date(timestamp) - 1]
# prices <- merge(prices, tokens, by.x = "tokenId", by.y = "Token")
# prices[, adj := 10**as.numeric(decimals)]
prices[, price := as.numeric(price) / 10**18]
prices[, max_block := max(as.numeric(updateAtBlockId)), by = tokenId]
prices[, tokenId := fixToken(tokenId)]
# prices[tokenId=="KSM"]

# Get the max block for each date and feed that to the python code
minDate <- today() - 32
history <- prices[, max(block), by = date] %>%
  setorder(date)
history[, blockDelta := V1 - shift(V1)]
block_history <- history[blockDelta > 5000 & date > minDate, V1]
write.csv(block_history, "history.csv", row.names = FALSE)
  
```

```{python, include=FALSE}
from substrateinterface import SubstrateInterface

import pandas as pd
import numpy as np
from datetime import date
history = pd.read_csv('history.csv', index_col = None)
block_history = history['x']
# block_id = block_history[30]


# pull in the balance for `acct` based on each block in `history`
def get_fees(url, acct, block_history):
    data = []
    try:
        substrate = SubstrateInterface(url)
        for j in block_history:
            hash = substrate.get_block_hash(block_id = j)        
            timestamp = substrate.query(module='Timestamp',storage_function='Now',block_hash=hash).value
            block = substrate.get_block_number(hash)
            # result = substrate.query('System', 'Account', params = [acct], block_hash = hash)
            # free = result.value['data']['free']
            balance_info = substrate.get_runtime_state(
                module='System',
                storage_function='Account',
                params=[acct],
                block_hash=hash
            ).get('result')
            balance = balance_info.get('data').get('free', 0) # / 10**12
            outi = {"Block": block, "Time": timestamp, 'Balance': balance}
            data.append(outi)
    except Exception as e:
        balance = None
    return data
            
# url = 'wss://acala-rpc-0.aca-api.network'
url = r.api_url
acct = '23M5ttkmR6KcoTAAE6gcmibnKFtVaTP5yxnY8HF1BmrJ2A1i'
fees_api = get_fees(url, acct, block_history)
fees_api = pd.DataFrame(fees_api)
fees_api['Time'] = pd.to_datetime(fees_api['Time'],unit='ms')

```

```{r treasury, cache = TRUE, include=FALSE}

# Treasury account from Python API fees
fees <- py$fees_api %>%
  as.data.table
fees[, Balance := as.numeric(Balance) / 10**12]

fees[, FeeIncome := Balance - shift(Balance, 1)]
fees[, date := as.Date(Time)]

# Stability fee
collaterParams <- getLoansCollateralParams_acala_loan(network)
collaterParams <- collaterParams[!duplicated(collateral.id), .(collateral.id, APR)]
if (network == 'Karura') {
  pos <- getLoansDailyPositions_acala_loan(network, window = 31, staging = TRUE) 
} else {
  pos <- getLoansDailyPositions_acala_loan(network, window = 31, staging = TRUE) 
}
pos <- pos[, .(timestamp, collateral.id, debitVolumeUSD, Date)]
pos <- merge(pos, collaterParams, by = "collateral.id", all.x = TRUE)
pos[, dailyAPR := APR / 365]
pos[, dailyFee := as.numeric(debitVolumeUSD) * dailyAPR]
fwrite(pos, file = network %+% "_stability_rawdata.csv")

dailyStability <- pos[, sum(dailyFee), by = Date] %>%
  setnames("V1", "Stability_Fee_USD") %>%
  setorder(Date)
fees <- merge(fees, dailyStability, by.x = 'date', by.y = 'Date', all.x = TRUE)

if (network == 'Karura') {
  # get bad debt penalty from subquery
  cdp2 <- getLiquidateUnsafeCDP_acala_loan(network, window = 31, staging = TRUE)
} else {
  cdp2 <- getLiquidateUnsafeCDP_acala_loan(network, window = 31, staging = TRUE)
}
cdp2[, block.id := as.numeric(block.id)]
cdp3 <- cdp2[, .(Date, badDebitVolumeUSD)]
fwrite(cdp3, file = network %+% "_cdp_rawdata.csv")

cdp4 <- cdp3[, sum(as.numeric(badDebitVolumeUSD)) * .15, by = Date] %>%
  setnames("V1", "CDP_Penalty_Fee_USD")
fees <- merge(fees, cdp4, by.x = 'date', by.y = 'Date', all.x = TRUE)
fees[is.na(CDP_Penalty_Fee_USD), CDP_Penalty_Fee_USD := 0]
liquidations <- cdp2[, .(block.id, timestamp, collateral.id, collateralVolumeUSD, badDebitVolumeUSD)]

# } else {
# 
#   # get bad debt penalty from subquery
#   cdp2 <- getLiquidateUnsafeCDP_acala_loan(network, window = 31, staging = TRUE)
#   cdp2[, block.id := as.numeric(block.id)]
#   cdp3 <- cdp2[, .(Date, badDebitVolumeUSD)]
#   fwrite(cdp3, file = network %+% "_cdp_rawdata.csv")
# 
#   cdp4 <- cdp3[, sum(as.numeric(badDebitVolumeUSD)) * .15, by = Date] %>%
#     setnames("V1", "CDP_Penalty_Fee_USD")
#   both <- merge(cdp_daily, cdp4, by.x = "date", by.y = "Date", all = TRUE)
#   
#   d <- list()
#   for (i in 1:20) {
#     cdp_rawdata <- get_subscan_events(nobs = 100, network = network, module = 'cdpengine', call = 'LiquidateUnsafeCDP', start_page = i, extract = TRUE)
#     cdp <- cdp_rawdata$cdpengine_LiquidateUnsafeCDP
#     cdp[, date := as.Date(time)]
#     d[[i]] <- cdp[time >= minDate]
#     if (min(cdp$date) < minDate) break
#   }
#   cdp <- rbindlist(d)
#   cdp[, CurrencyId := fixToken(CurrencyId)]
#   cdp[, block_num := as.numeric(block_num)]
#   cdp[, BadDebtValue := as.numeric(BadDebtValue) / 10**12]
#   cdp[, CollateralAmount := as.numeric(CollateralAmount) / 10**12]
#   fwrite(cdp, file = network %+% "_cdp_rawdata.csv")
# 
#   cdp_daily <- cdp[, sum(as.numeric(BadDebtValue)) * .15, by = date] %>%
#     setnames("V1", "CDP_Penalty_Fee_USD")
#   fees <- merge(fees, cdp_daily, by.x = 'date', by.y = 'date', all.x = TRUE)
#   liquidations <- cdp[, .(block_num, time, CurrencyId, CollateralAmount, BadDebtValue)]
# 
# }
fees[is.na(CDP_Penalty_Fee_USD), CDP_Penalty_Fee_USD := 0]
fees <- tail(fees, 30)
fees7 <- tail(fees, 7)

sum30 <- fees[, .(sum(FeeIncome, na.rm = TRUE), 
                  sum(Stability_Fee_USD, na.rm = TRUE),
                  sum(CDP_Penalty_Fee_USD, na.rm = TRUE))] %>%
  setnames(c("V1", "V2", "V3"), 
           c("30D Sum FeeIncome_" %+% nativeToken, "30D Sum Stability_Fee_USD", "30D Sum CDP_Penalty_Fee_USD"))

sum7 <- fees7[, .(sum(FeeIncome, na.rm = TRUE), 
                  sum(Stability_Fee_USD, na.rm = TRUE),
                  sum(CDP_Penalty_Fee_USD, na.rm = TRUE))] %>%
  setnames(c("V1", "V2", "V3"), 
           c("7D Sum FeeIncome_" %+% nativeToken, "7D Sum Stability_Fee_USD", "7D Sum CDP_Penalty_Fee_USD"))

# test <- merge(cdp[, .(date, CurrencyId, block_num, BadDebtValue)],
#               cdp2[, .(Date, collateral.id, block.id, badDebitVolumeUSD)],
#               by.x = c('CurrencyId', 'block_num'),
#               by.y = c('collateral.id', 'block.id'),
#               all = TRUE)
# View(test)


# liquid staking fee
filter <- ' filter: {accountId: {equalTo: "' %+% addr1 %+% '"}}'
dat1 <- getDailyAccountBalance_acala_token(network, window = 31, filter = filter)
price <- prices[updateAtBlockId == max_block, .(tokenId, price)]
dat1 <- merge(dat1, 
              prices[, .(tokenId, timestamp, price)], 
              by = c("timestamp","tokenId"), 
              all.x = TRUE)
dat1[, balanceUSD := free * price]
data1 <- dat1[free > 1, .(tokenId, timestamp, free, price, balanceUSD)]


# builtin1 <- try(subscanr::get_subscan_account_tokens(network = network, addr))
# while (inherits(builtin1, "try-error")) {
#   Sys.sleep(3)
#   builtin1 <- try(subscanr::get_subscan_account_tokens(network, addr))
# }
# time <- as.POSIXct(builtin1$generated_at, origin = "1970-01-01", tz = 'UTC')
# data1 <- builtin1$data$builtin %>%
#   as.data.table
# data1[, balance := as.numeric(balance) / 10**as.numeric(decimals)]
# price <- prices[updateAtBlockId == max_block, .(tokenId, price)]
# data1 <- merge(data1, price, by.x = "symbol", by.y = "tokenId", all.x = TRUE)
# data1[, balanceUSD := balance * price]
# data1[, .(symbol, balance, price, balanceUSD)]

# Stablecoin stability fee + liquidation fee, aUSD balance
filter <- ' filter: {accountId: {equalTo: "' %+% addr1 %+% '"}}'
dat2 <- getDailyAccountBalance_acala_token(network, window = 31, filter = filter)
price <- prices[updateAtBlockId == max_block, .(tokenId, price)]
dat2 <- merge(dat2, 
              prices[, .(tokenId, timestamp, price)], 
              by = c("timestamp","tokenId"), 
              all.x = TRUE)
dat2[, balanceUSD := free * price] 
setorder(dat2, tokenId, timestamp)
data2 <- dat2[free > 1, .(tokenId, timestamp, free, price, balanceUSD)]

# addr <- "qmmNufxeWaAUp4SVa1Vi1owrzP1xhR6XKdorEcck17RF498"; network = "Karura"
# builtin2 <- try(subscanr::get_subscan_account_tokens(network, addr))
# while (inherits(builtin2, "try-error")) {
#   Sys.sleep(3)
#   builtin2 <- try(subscanr::get_subscan_account_tokens(network, addr))
# }
# time <- as.POSIXct(builtin2$generated_at, origin = "1970-01-01", tz = 'UTC')
# data2 <- builtin2$data$builtin %>% 
#   as.data.table
# data2[, balance := as.numeric(balance) / 10**as.numeric(decimals)]
# price <- prices[updateAtBlockId == max_block, .(tokenId, price)]
# data2 <- merge(data2, price, by.x = "symbol", by.y = "tokenId", all.x = TRUE)
# data2[, balanceUSD := balance * price]
# data2[, .(symbol, balance, price, balanceUSD)]

# It should have a section display the current holding and dollar value,
# last 7 day income, last 30 day income, some charts if you have time
```

# `r network` Income {.tabset}

Row
----

### 7D Fee Income

```{r sum7}

knitr::kable(sum7, escape = FALSE, format.args = list(big.mark = ",")) %>%
  kable_styling()

```

Row
----

### 30D Fee Income

```{r sum30}

knitr::kable(sum30, escape = FALSE, format.args = list(big.mark = ",")) %>%
  kable_styling()

dat <- fees[, .(date, FeeIncome, Stability_Fee_USD, CDP_Penalty_Fee_USD)]
main <- network %+% " Daily Fees"
dygraph(dat, main = main)  %>% 
    dySeries("FeeIncome", stepPlot = TRUE, fill = TRUE)

rm(dat)

```

Row
----

### Daily Fee Data

```{r daily}

knitr::kable(fees, escape = FALSE, format.args = list(big.mark = ",")) %>%
  kable_styling()

```

# `r network` Balances {.tabset}

Row
----

### Balance for account `r addr1`

```{r data1}

knitr::kable(data1[timestamp == max(timestamp)], escape = FALSE, format.args = list(big.mark = ",")) %>%
  kable_styling()

dat <- data1[, .(timestamp, tokenId, balanceUSD)]
dat[, timestamp := as.Date(timestamp)]
us <- unique(dat$tokenId)

main <- network %+% " " %+% us[1] %+% " Treasury in " %+% addr1
dygraph(dat[tokenId == us[1], .(timestamp, balanceUSD)], main = main)  %>% 
    dySeries("balanceUSD", stepPlot = TRUE, fill = TRUE)

if (length(us) > 1) {
  main <- network %+% " " %+% us[2] %+% " Treasury in " %+% addr1
  dygraph(dat[tokenId == us[2], .(timestamp, balanceUSD)], main = main)  %>% 
      dySeries("balanceUSD", stepPlot = TRUE, fill = TRUE)
}

rm(dat)

```

Row
----

### Balance for account `r addr2`

```{r data2}

knitr::kable(data2[timestamp == max(timestamp)], escape = FALSE, format.args = list(big.mark = ",")) %>%
  kable_styling()

dat <- data2[, .(timestamp, tokenId, balanceUSD)]
dat[, timestamp := as.Date(timestamp)]
us <- unique(dat$tokenId)

main <- network %+% " " %+% us[1] %+% " Treasury in " %+% addr2
dygraph(dat[tokenId == us[1], .(timestamp, balanceUSD)], main = main)  %>% 
    dySeries("balanceUSD", stepPlot = TRUE, fill = TRUE)

if (length(us) > 1) {
  main <- network %+% " " %+% us[2] %+% " Treasury in " %+% addr2
  dygraph(dat[tokenId == us[2], .(timestamp, balanceUSD)], main = main)  %>% 
      dySeries("balanceUSD", stepPlot = TRUE, fill = TRUE)
}

rm(dat)

```